package org.eduapp.trains.dao.train.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.eduapp.trains.DAOException;
import org.eduapp.trains.dao.BaseDAOImpl;
import org.eduapp.trains.dao.train.TrainDAO;
import org.eduapp.trains.model.train.ModelTrain;
import org.eduapp.trains.utils.UtilsString;
import org.eduapp.trains.vo.VoTrainSearch;
import org.eduapp.trains.web.support.PaginationSupport;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;

public class TrainDAOImpl extends BaseDAOImpl<ModelTrain> implements TrainDAO {
	
	private final static Logger logger = Logger.getLogger(TrainDAOImpl.class);
	
	/*
	 * (non-Javadoc)
	 * @see org.eduapp.trains.dao.train.TrainDAO#getByTrainNo(java.lang.String)
	 */
	@SuppressWarnings("unchecked")
	public ModelTrain getByTrainNo (String trainNo) throws DAOException {
		
		if (UtilsString.isNotEmpty(trainNo)) {
			
			DetachedCriteria criteria = DetachedCriteria.forClass(ModelTrain.class);
			criteria.add(Restrictions.ilike("trainNumber", trainNo, MatchMode.EXACT));
			List<ModelTrain> result = (List<ModelTrain>)this.getListByCriteria(criteria);
			
			return result != null && result.size() > 0 ? result.get(0) : null;
		}
		
		return null;
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.eduapp.trains.dao.train.TrainDAO#getByStation2Station(java.lang.String, java.lang.String, int, int, java.lang.String, java.lang.Boolean)
	 */
	public PaginationSupport<ModelTrain> getByStation2Station (String stationSrc, String stationDst,
			int offset, int pageSize, String order, Boolean isDesc) throws DAOException {
		return getByStation2Station(stationSrc, stationDst, offset, pageSize, order, isDesc, null);
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.eduapp.trains.dao.train.TrainDAO#getByStation2Station(java.lang.String, java.lang.String, int, int, java.lang.String, java.lang.Boolean)
	 */
	@SuppressWarnings("unchecked")
	public PaginationSupport<ModelTrain> getByStation2Station (String stationSrc, String stationDst,
			int offset, int pageSize, String order, Boolean isDesc, VoTrainSearch vo) throws DAOException {
		if (UtilsString.isNotEmpty(stationSrc) && UtilsString.isNotEmpty(stationDst)) {
			if(stationDst.equals(stationSrc)){
				return null ;
			}
			String sqlTotal = 
				"SELECT COUNT(*) FROM edu_trains R WHERE R.train_id IN " +
				"(SELECT B1.train_id FROM edu_trains_schedule B1 LEFT JOIN " +
				"(SELECT B2.train_id, B2.train_station_seq FROM edu_trains_schedule B2 Where 1=1" ;
			if(stationSrc.indexOf("站")>-1){
				sqlTotal = sqlTotal+" and B2.train_station_name = '"+stationSrc.replace("站", "")+"') B ";
			}else{
				sqlTotal = sqlTotal+" and B2.train_station_name LIKE '%" + stationSrc + "%') B " ;
			}
			if(stationDst.indexOf("站")>-1){
				sqlTotal = sqlTotal+"ON B1.train_id = B.train_id WHERE B1.train_station_seq > B.train_station_seq AND " +
				"B1.train_station_name = '" + stationDst.replace("站", "") + "')";
			}else{
				sqlTotal = sqlTotal+"ON B1.train_id = B.train_id WHERE B1.train_station_seq > B.train_station_seq AND " +
				"B1.train_station_name LIKE '%" + stationDst + "%')";
			}
			
			
				
			
				 //"SELECT COUNT(*) FROM edu_trains WHERE train_id IN " +  
				 //"(SELECT train_id FROM edu_trains_schedule WHERE train_id IN " +
				 //" (SELECT train_id FROM `edu_trains_schedule` WHERE `train_station_name` LIKE '%" + stationSrc + "%') " + 
				 //"AND train_station_name LIKE '%" + stationDst + "%') ";
			
			List<Object> total = findListByNativeSQL(sqlTotal);
			
			int count = 0;
			
			if (total != null) {
				count = Integer.parseInt(total.get(0).toString());
			}
			
			if (count > 0) {
			
				String nativeSql = 
					"SELECT  R.train_id FROM edu_trains R WHERE R.train_id IN " +
					"(SELECT B1.train_id FROM edu_trains_schedule B1 LEFT JOIN " +
					"(SELECT B2.train_id, B2.train_station_seq FROM edu_trains_schedule B2 Where 1=1" ;
				if(stationSrc.indexOf("站")>-1){
					nativeSql = nativeSql+" and B2.train_station_name = '"+stationSrc.replace("站", "")+"') B ";
				}else{
					nativeSql = nativeSql+" and B2.train_station_name LIKE '%" + stationSrc + "%') B " ;
				}
				if(stationDst.indexOf("站")>-1){
					nativeSql = nativeSql+"ON B1.train_id = B.train_id WHERE B1.train_station_seq > B.train_station_seq AND " +
					"B1.train_station_name = '" + stationDst.replace("站", "") + "')";
				}else{
					nativeSql = nativeSql+"ON B1.train_id = B.train_id WHERE B1.train_station_seq > B.train_station_seq AND " +
					"B1.train_station_name LIKE '%" + stationDst + "%')";
				}
				
				/**String nativeSql = 
					"SELECT R.train_id FROM edu_trains R WHERE R.train_id IN " +
					"(SELECT B1.train_id FROM edu_trains_schedule B1 LEFT JOIN " +
					"(SELECT B2.train_id, B2.train_station_seq FROM edu_trains_schedule B2 WHERE B2.train_station_name LIKE '%" + stationSrc + "%') B " +
					"ON B1.train_id = B.train_id WHERE B1.train_station_seq > B.train_station_seq AND " +
					"B1.train_station_name LIKE '%" + stationDst + "%') and  R.train_terminating_station<>'北京'";
				*/
				//	 "SELECT train_id FROM edu_trains WHERE train_id IN " +  
				//	 "(SELECT train_id FROM edu_trains_schedule WHERE train_id IN " +
				//	 " (SELECT train_id FROM `edu_trains_schedule` WHERE `train_station_name` LIKE '%" + stationSrc + "%') " + 
				//	 "AND train_station_name LIKE '%" + stationDst + "%') ";
				
				order = UtilsString.isNotEmpty(order) ? order : "train_number";
				
				if (UtilsString.isNotEmpty(order)) {
					if (isDesc != null && isDesc) {
						nativeSql += "ORDER BY " + order + " DESC ";
					} else {
						nativeSql += "ORDER BY " + order + " ASC ";
					}
				}
				
				//nativeSql = "select * from ("+nativeSql+") a where a.train_station_name<>";
				nativeSql += "LIMIT " + offset + "," + pageSize;
				
			
				List<Object> items = findListByNativeSQL(nativeSql);
				
				if (items != null) {
					List<ModelTrain> modelTrains = new ArrayList<ModelTrain>();
					for (Object obj : items) {
						ModelTrain model = get(obj.toString());
						if (vo != null) {
							model.setTrainSearchVO(vo);
						}
						modelTrains.add(model);
					}
					
					return new PaginationSupport<ModelTrain> (modelTrains, count, offset, 
							pageSize > 0 ? pageSize : count);
				}
				
			}
		}
		return null;
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.eduapp.trains.dao.train.TrainDAO#getByStations(java.lang.String)
	 */
	@SuppressWarnings("unchecked")
	public PaginationSupport<ModelTrain> getByStations(String stationName, int offset, int pageSize, String order, Boolean isDesc) throws DAOException {
		
		if (UtilsString.isNotEmpty(stationName)) {
			
			String sqlCount = 
				"SELECT COUNT(*) FROM edu_trains where train_id IN " +
				"(SELECT train_id FROM `edu_trains_schedule` WHERE `train_station_name` LIKE '%" + stationName + "%')";
			
			List<Object> total = findListByNativeSQL(sqlCount);
			
			int count = 0;
			
			if (total != null) {
				count = Integer.parseInt(total.get(0).toString());
			}
			
			if (count > 0) {
				
				String nativeSql = 
					"SELECT train_id FROM edu_trains where train_id IN " +
					"(SELECT train_id FROM `edu_trains_schedule` WHERE `train_station_name` LIKE '%" + stationName + "%')";
				
				order = UtilsString.isNotEmpty(order) ? order : "train_number";
				
				if (UtilsString.isNotEmpty(order)) {
					if (isDesc != null && isDesc) {
						nativeSql += "ORDER BY " + order + " DESC ";
					} else {
						nativeSql += "ORDER BY " + order + " ASC ";
					}
				}
				
				nativeSql += "LIMIT " + offset + "," + pageSize;
				
				List<Object> result = findListByNativeSQL(nativeSql);
				
				if (result != null) {
					List<ModelTrain> modelTrains = new ArrayList<ModelTrain>();
					for (Object obj : result) {
						modelTrains.add(get(obj.toString()));
					}
					
					return new PaginationSupport<ModelTrain> (modelTrains, count, offset, 
							pageSize > 0 ? pageSize : count);
					
				}
			}		
		}
		return null;
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.eduapp.trains.dao.train.TrainDAO#getAll()
	 */
	public List<ModelTrain> getAll () throws DAOException {
		return this.findListByNamedQuery("modelTrain.fetchAll");
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.eduapp.trains.dao.train.TrainDAO#getTrainToBeUpdated()
	 */
	public List<ModelTrain> getTrainToBeUpdated() throws DAOException {
		return this.findListByNamedQuery("modelTrain.fetchToBeUpdated");
	}
	
	public static Logger getLogger() {
		return logger;
	}
	
}
